package com.salesys.gr.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 封装数据库的常用操作
 */
public class DbUtil {
	/** 驱动 */
	private static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
	/** 路径 */
	private static final String DBURL = "jdbc:mysql://221.231.138.47:3306/tony";
	/** 用户名 */
	private static final String DBUSER = "tony";
	/** 密码 */
	private static final String DBPASS = "tonyarmand";
	/** 数据库建立连接 */
	private static Connection conn = null;
	/** SQL语句 */
	private static PreparedStatement pstmt = null;
	/** 结果集 */
	private static ResultSet rs;
	private static int affectRow;

	/**
	 * 取得Connection
	 * 
	 * @return
	 */
	public static void getConnection() {
		if (conn == null) {
			// for debug
			// System.out.println("尝试数据库连接");
			try {
				Class.forName(DBDRIVER); // 加载驱动
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
			try {
				conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS); // 连接数据库
			} catch (SQLException e) {
				e.printStackTrace();
			}
			// for debug
			// System.out.println("数据库连接成功");
		}
	}

	/**
	 * 关闭数据库连接
	 */
	public static void closeConnection() {
		// for debug
		// System.out.println("尝试关闭数据库连接");
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		// for debug
		// System.out.println("关闭数据库连接成功");
	}

	/**
	 * 设置PrepareStatement
	 * 
	 * @param sql
	 */
	public static void setPerpareStatement(String sql) {
		// for debug
		// System.out.println("尝试设置PrepareStatement");
		try {
			pstmt = conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// for debug
		// System.out.println("设置PrepareStatement成功为" + sql);
	}

	/**
	 * 设置PrepareStatement参数
	 * 
	 * @param id
	 * @param para
	 */
	public static void setPrepareStatementString(int id, String para) {
		// for debug
		// System.out.println("尝试设置PrepareStatement参数");
		try {
			pstmt.setString(id, para);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// for debug
		// System.out.println("设置PrepareStatement参数成功，序号'" + id + "'为'" + para + "'");
	}

	/**
	 * 执行PrepareStatement
	 */
	public static void queryPrepareStatement() {
		// for debug
		// System.out.println("尝试执行PrepareStatement");
		try {
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// for debug
		// System.out.println("执行PrepareStatement成功");
	}

	/**
	 * 执行PrepareStatement
	 */
	public static void updatePrepareStatement() {
		// for debug
		// System.out.println("尝试执行PrepareStatement");
		try {
			affectRow = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// for debug
		// System.out.println("执行PrepareStatement成功");
	}

	/**
	 * 关闭PrepareStatement
	 */
	public static void closePrepareStatement() {
		// for debug
		// System.out.println("尝试关闭PrepareStatement");
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		// for debug
		// System.out.println("关闭PrepareStatement成功");
	}

	/**
	 * 返回ResultSet
	 */
	public static ResultSet getResultSet() {
		// for debug
		// System.out.println("返回ResultSet");
		return rs;
	}

	/**
	 * 返回affectRow
	 */
	public static int getAffectRow() {
		// for debug
		// System.out.println("返回affectRow为" + affectRow);
		return affectRow;
	}

	/**
	 * 关闭结果集
	 */
	public static void closeResultSet() {
		// for debug
		// System.out.println("尝试关闭ResultSet");
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		// for debug
		// System.out.println("关闭ResultSet成功");
	}

	/**
	 * 关闭DbUtil
	 */
	public static void close() {
		// for debug
		// System.out.println("尝试关闭DbUtil");
		closePrepareStatement();
		closeResultSet();
		// for debug
		// System.out.println("关闭DbUtil成功");
	}
}